package DBLayer;
import java.sql.*;
import java.util.ArrayList;
import ModelLayer.*;
/**
 * @author 
 */
public class DBKunde 
{
	/**
	 * Initializes instance variables
	 */
	private  Connection con;
	
    /** Creates connection to the database and creates an instance of DBKunde*/
    public DBKunde() {
      con = DbConnection1.getInstance().getDBcon();
    }
    
    /** getAllKunder finds all Kunder with
     * 
     * @param retriveAssociation (Kunde does not have any foreign keys)
     * @return
     */
    
    public ArrayList<Kunde> getAllKunder(boolean retriveAssociation)
    {
        return miscWhere("", retriveAssociation);
    }
    /**
     * getKunde finds one kunde with
     * @param telefonNr
     * @param retriveAssociation (Kunde does not have any foreign keys)
     * @return
     */
    public Kunde getKunde(int telefonNr, boolean retriveAssociation)
    {   String wClause = "  telefonNr = '" + telefonNr + "'";
        return singleWhere(wClause, retriveAssociation);
    }
    /**
     * searchKundeNavn finds a kunde with
     * @param navn
     * @param retriveAssociation (Kunde does not have any foreign keys)
     * 
     * @return
     */
    public Kunde searchKundeNavn(String navn, boolean retriveAssociation)
    {
        String wClause = "navn like '%" + navn + "%'";
        //System.out.println("SearchKunde " + wClause);
        return singleWhere(wClause, retriveAssociation);
    }
   
    /** insertKunde  creates a kunde SQL query to be inserted into database with
	 * 
     * @param kunde
     * @return
     * @throws Exception (If Kunde not created)
     */
  
    public int insertKunde(Kunde kunde) throws Exception
    {  //call to get the next telefonNr
       int rc = -1;
       
	   String query="INSERT INTO kunde(telefonNr, navn, email)  VALUES('"+
	  		kunde.getTelefonNr()  + "','"  +
	  		kunde.getNavn()  + "','"  +
            kunde.getEmail() + "')";

     // System.out.println("insert : " + query);
      try{ // insert new kunde
          Statement stmt = con.createStatement();
          stmt.setQueryTimeout(5);
     	  rc = stmt.executeUpdate(query);
          stmt.close();
      }//end try
       catch(SQLException ex){
          //System.out.println("Kunde ikke oprettet");
          throw new Exception ("Kunde is not inserted correct");
       }
       return(rc);
    }

    /** updateKunde creates an update kunde SQL query to update a kunde in the database with
     * 
     * @param kunde
     * @return
     */
	public int updateKunde(Kunde kunde)
	{
	Kunde kObj  = kunde;
		int rc=-1;

		String query="UPDATE kunde SET "+
		 	  "navn ='"+ kObj.getNavn()+"', "+
		 	  "email ='"+kObj.getEmail() +                         
		          "' WHERE telefonNr = "+ kObj.getTelefonNr() + "";
               // System.out.println("Update query:" + query);
  		try{ // update kunde
	 		Statement stmt = con.createStatement();
	 		stmt.setQueryTimeout(5);
	 	 	rc = stmt.executeUpdate(query);

	 	 	stmt.close();
		}//slut try
	 	catch(Exception ex){
	 	 	//System.out.println("Update exception in kunde db: "+ex);
	  	}
		return(rc);
	}	
	
	/** singleWhere is used when we only want to select one Kunde
	 * 
	 * @param wClause
	 * @param retrieveAssociation (Kunde does not have any foreign keys)
	 * @return
	 */
	
		private Kunde singleWhere(String wClause, boolean retrieveAssociation)
		{
			ResultSet results;
			Kunde kObj = new Kunde();
	                
		        String query =  buildQuery(wClause);
	                //System.out.println(query);
			try{ // read the kunde from the database
		 		Statement stmt = con.createStatement();
		 		stmt.setQueryTimeout(5);
		 		results = stmt.executeQuery(query);
		 		
		 		if( results.next() ){
	                            kObj = buildKunde(results);
	                            //Association is to be build
	                            stmt.close();
		 		}                 
			}//end try	
		 	catch(Exception e){
		 		System.out.println("Query exception: "+e);
		 	}
			//System.out.println(kObj.getTelefonNr());
			return kObj;
		}
		
		/**mischWere is used whenever we want to select more than one Kunde or all Kunder
		 * 
		 * @param wClause
		 * @param retrieveAssociation (Kunde does not have any foreign keys)
		 * @return
		 */
		
	private ArrayList<Kunde> miscWhere(String wClause, boolean retrieveAssociation)
	{
            ResultSet results;
	    ArrayList<Kunde> list = new ArrayList<Kunde>();	
		
	    String query =  buildQuery(wClause);
  
            try{ // read the kunde from the database
		Statement stmt = con.createStatement();
	 	stmt.setQueryTimeout(5);
	 	results = stmt.executeQuery(query);
	 	
		while( results.next() ){
			Kunde kObj = new Kunde();
		 kObj = buildKunde(results);	
                 list.add(kObj);
         		//System.out.println("Navn: " + kObj.getNavn() +  "	Telefonnummer: " + kObj.getTelefonNr() + "	email: " + kObj.getEmail());
		}//end while
		stmt.close();     
                 
		}//end try	
	 	catch(Exception e){
	 		//System.out.println("Query exception - select: "+e);
			e.printStackTrace();
	 	}
		return list;
	}
	
	/**
	 * buildQuery uses wClause insert String list of the attributes in a Kunde and builds the
	 * WHERE part of the query that is to be committed to the SQl database
	 * @param wClause 
	 * @return
	 */
	private String buildQuery(String wClause)
	{
	    String query="SELECT * FROM kunde";
		
		if (wClause.length()>0)
			query=query+" WHERE "+ wClause;
			
		return query;
	}
	
	/** buildKunde creates a kunde object with
	 * 
	 * @param results (used to bring attributes to fObj)
	 * @return
	 */
	private Kunde buildKunde(ResultSet results)
      {   
		  Kunde kObj = new Kunde();
          try
          { // the columns from the table kunde are used
                kObj.setTelefonNr(results.getInt("telefonNr"));
                kObj.setNavn(results.getString("navn"));
                kObj.setEmail(results.getString("email"));
               
          }
         catch(Exception e)
         {
             //System.out.println("error in building the kunde object");
         }
         return kObj;
      }
 
}
